﻿using Md.Api.Entity.DapperModels.MdDB;

namespace Md.Dapper.Repositories.Md;
/// <summary>
/// 产品订单仓储
/// </summary>
public class MdProductOrderRep : BaseRep<MdProductOrderModel>
{
    private static string sqlFields = string.Format(@"
        `order_id`,
        `product_id`,
        `user_id`,
        `address_id`,
        `callback_url`,
        `status`,
        `del_flag`,
        `reason`,
        `create_by`,
        `create_time`,
        `update_by`,
        `update_time`,
        `remark`
    ");

    /// <summary>
    /// 通过编号获取订单详情
    /// </summary>
    /// <param name="id">订单编号</param>
    /// <returns>MdProductOrderModel</returns>
    public MdProductOrderModel? GetMdProductOrderById(long id)
    {
        #region ===sql===
        string sqlStr = string.Format(@"
            select o.* from
            (
                select mpo.order_id ,
                mpo.product_id ,
                mpo.user_id ,
                mpo.address_id ,
                mpo.callback_url ,
                mpo.status ,
                mpo.del_flag,
                mpo.reason,
                mpo.create_time,
                mpo.create_by ,
                mpo.update_by ,
                mpo.update_time,
                mpo.remark ,
                mp.title as 'product_title' ,
                mp.description as 'product_description' ,
                mp.quantity as 'product_quantity' ,
                mp.market_price as 'product_market_price' ,
                mp.seliing_price as 'product_seliing_price' ,
                mp.purchase_link as 'product_purchase_link' ,
                mp.content as 'product_content' ,
                mp.illustrate as 'product_illustrate' ,
                mp.picture as 'product_picture' ,
                mp.thum_picture as 'product_thum_picture' ,
                mp.category as 'product_category' ,
                mp.create_time as 'product_create_time' ,
                mu.email as 'user_email' ,
                mu.avatar as 'user_avatar' ,
                mu.user_name as 'user_name' ,
                mu.user_nick as 'user_nick' ,
                mu.user_type as 'user_type',
                mu.sex as 'user_sex' ,
                mu.phone as 'user_phone' ,
                mu.status as 'user_status' ,
                mu.create_time as 'user_create_time' ,
                mu.integral as 'user_integral' ,
                mu.open_id as 'user_open_id' ,
                ma.state as 'address_state',
                ma.city as 'address_city' ,
                ma.province as 'address_province' ,
                ma.region as 'address_region' ,
                ma.country as 'address_country' ,
                ma.zip_code as 'address_zip_code' ,
                ma.address as 'address_detail' ,
                ma.name as 'address_name' ,
                ma.email as 'address_email' ,
                ma.phone as 'address_phone'
                from md_product_order mpo 
                left join md_product mp on mp.product_id = mpo.product_id 
                left join md_user mu on mu.user_id = mpo.user_id 
                left join md_address ma on ma.address_id = mpo.address_id 
            ) as o where 1 =1 and o.del_flag = '0' and o.order_id = @OrderId
        ");
        #endregion

        return QuerySingle(sqlStr, new { OrderId = id });
    }

    /// <summary>
    /// 分页获取订单列表
    /// </summary>
    /// <param name="model">查询条件</param>
    /// <param name="orderBy">排序字段</param>
    /// <param name="isDesc">是否倒叙</param>
    /// <returns>Page<MdProductOrderModel></returns>
    public Page<MdProductOrderModel> GetMdUserExperiencerPageList(MdProductOrderModel model, int pageIndex, int pageSize, string orderBy, bool isDesc)
    {
        // 重新计算当前页
        pageIndex = (pageIndex - 1) * pageSize;

        string sqlWhere = string.Empty;

        #region ===where===
        if (!string.IsNullOrWhiteSpace(model.ProductTitle))
        {
            sqlWhere += " and o.product_title like concat('%', @ProductTitle, '%') ";
        }
        if (!string.IsNullOrWhiteSpace(model.ProductCategory))
        {
            sqlWhere += " and o.product_category = @ProductCategory ";
        }

        if (!string.IsNullOrWhiteSpace(model.UserEmail))
        {
            sqlWhere += " and o.user_email like concat('%', @UserEmail, '%') ";
        }
        if (!string.IsNullOrWhiteSpace(model.UserName))
        {
            sqlWhere += " and o.user_name like concat('%', @UserName, '%') ";
        }
        if (!string.IsNullOrWhiteSpace(model.UserNick))
        {
            sqlWhere += " and o.user_nick like concat('%', @UserNick, '%') ";
        }
        if (!string.IsNullOrWhiteSpace(model.UserType))
        {
            sqlWhere += " and o.user_type = @UserType ";
        }
        if (!string.IsNullOrWhiteSpace(model.UserSex))
        {
            sqlWhere += " and o.user_sex = @UserSex ";
        }
        if (!string.IsNullOrWhiteSpace(model.UserPhone))
        {
            sqlWhere += " and o.user_phone like concat('%', @UserPhone, '%') ";
        }

        if(!string.IsNullOrWhiteSpace(model.AddressState))
        {
            sqlWhere += " and o.address_state like concat('%', @AddressState, '%') ";
        }
        if (!string.IsNullOrWhiteSpace(model.AddressCity))
        {
            sqlWhere += " and o.address_city like concat('%', @AddressCity, '%') ";
        }
        if (!string.IsNullOrWhiteSpace(model.AddressProvince))
        {
            sqlWhere += " and o.address_province like concat('%', @AddressProvince, '%') ";
        }
        if (!string.IsNullOrWhiteSpace(model.AddressRegion))
        {
            sqlWhere += " and o.address_region like concat('%', @AddressRegion, '%') ";
        }
        if (!string.IsNullOrWhiteSpace(model.AddressCountry))
        {
            sqlWhere += " and o.address_country like concat('%', @AddressCountry, '%') ";
        }
        if (!string.IsNullOrWhiteSpace(model.AddressZipCode))
        {
            sqlWhere += " and o.address_zip_code like concat('%', @AddressZipCode, '%') ";
        }
        if (!string.IsNullOrWhiteSpace(model.AddressName))
        {
            sqlWhere += " and o.address_name like concat('%', @AddressName, '%') ";
        }
        if (!string.IsNullOrWhiteSpace(model.AddressEmail))
        {
            sqlWhere += " and o.address_email like concat('%', @AddressEmail, '%') ";
        }
        if (!string.IsNullOrWhiteSpace(model.AddressPhone))
        {
            sqlWhere += " and o.address_phone like concat('%', @AddressPhone, '%') ";
        }



        if (model.ProductId > 0)
        {
            sqlWhere += " and o.product_id = @ProductId ";
        }
        if (model.UserId > 0)
        {
            sqlWhere += " and o.user_id = @UserId ";
        }
        if (model.AddressId > 0)
        {
            sqlWhere += " and o.address_id = @AddressId ";
        }
        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlWhere += " and o.status = @Status ";
        }
        if (!string.IsNullOrWhiteSpace(model.CreateBy))
        {
            sqlWhere += " and o.create_by = @CreateBy ";
        }
        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlWhere += " and o.update_by = @UpdateBy ";
        }

        sqlWhere += " and o.del_flag = '0' ";
        #endregion

        #region ===sql===
        string sqlStr = string.Format(@"
            select o.* from
            (
                select mpo.order_id ,
                mpo.product_id ,
                mpo.user_id ,
                mpo.address_id ,
                mpo.callback_url ,
                mpo.status ,
                mpo.del_flag,
                mpo.reason,
                mpo.create_time,
                mpo.create_by ,
                mpo.update_by ,
                mpo.update_time,
                mpo.remark ,
                mp.title as 'product_title' ,
                mp.description as 'product_description' ,
                mp.quantity as 'product_quantity' ,
                mp.market_price as 'product_market_price' ,
                mp.seliing_price as 'product_seliing_price' ,
                mp.purchase_link as 'product_purchase_link' ,
                mp.content as 'product_content' ,
                mp.illustrate as 'product_illustrate' ,
                mp.picture as 'product_picture' ,
                mp.thum_picture as 'product_thum_picture' ,
                mp.category as 'product_category' ,
                mp.create_time as 'product_create_time' ,
                mu.email as 'user_email' ,
                mu.avatar as 'user_avatar' ,
                mu.user_name as 'user_name' ,
                mu.user_nick as 'user_nick' ,
                mu.user_type as 'user_type',
                mu.sex as 'user_sex' ,
                mu.phone as 'user_phone' ,
                mu.status as 'user_status' ,
                mu.create_time as 'user_create_time' ,
                mu.integral as 'user_integral' ,
                mu.open_id as 'user_open_id' ,
                ma.state as 'address_state',
                ma.city as 'address_city' ,
                ma.province as 'address_province' ,
                ma.region as 'address_region' ,
                ma.country as 'address_country' ,
                ma.zip_code as 'address_zip_code' ,
                ma.address as 'address_detail' ,
                ma.name as 'address_name' ,
                ma.email as 'address_email' ,
                ma.phone as 'address_phone'
                from md_product_order mpo 
                left join md_product mp on mp.product_id = mpo.product_id 
                left join md_user mu on mu.user_id = mpo.user_id 
                left join md_address ma on ma.address_id = mpo.address_id 
            ) as o where 1 = 1 {0} {1} limit {2},{3}
        ", sqlWhere, GetOrderByDesc(!string.IsNullOrWhiteSpace(orderBy) ? $"o.{orderBy}" : "o.create_time", isDesc), pageIndex, pageSize);
        #endregion

        var list= Query(sqlStr, model);

        #region sqlCount
        string sqlCountStr = string.Format(@"
           select count(*) from
            (
                select mpo.order_id ,
                mpo.product_id ,
                mpo.user_id ,
                mpo.address_id ,
                mpo.callback_url ,
                mpo.status ,
                mpo.del_flag,
                mpo.reason,
                mpo.create_time,
                mpo.create_by ,
                mpo.update_by ,
                mpo.update_time,
                mpo.remark ,
                mp.title as 'product_title' ,
                mp.description as 'product_description' ,
                mp.quantity as 'product_quantity' ,
                mp.market_price as 'product_market_price' ,
                mp.seliing_price as 'product_seliing_price' ,
                mp.purchase_link as 'product_purchase_link' ,
                mp.content as 'product_content' ,
                mp.illustrate as 'product_illustrate' ,
                mp.picture as 'product_picture' ,
                mp.thum_picture as 'product_thum_picture' ,
                mp.category as 'product_category' ,
                mp.create_time as 'product_create_time' ,
                mu.email as 'user_email' ,
                mu.avatar as 'user_avatar' ,
                mu.user_name as 'user_name' ,
                mu.user_nick as 'user_nick' ,
                mu.user_type as 'user_type',
                mu.sex as 'user_sex' ,
                mu.phone as 'user_phone' ,
                mu.status as 'user_status' ,
                mu.create_time as 'user_create_time' ,
                mu.integral as 'user_integral' ,
                mu.open_id as 'user_open_id' ,
                ma.state as 'address_state',
                ma.city as 'address_city' ,
                ma.province as 'address_province' ,
                ma.region as 'address_region' ,
                ma.country as 'address_country' ,
                ma.zip_code as 'address_zip_code' ,
                ma.address as 'address_detail' ,
                ma.name as 'address_name' ,
                ma.email as 'address_email' ,
                ma.phone as 'address_phone'
                from md_product_order mpo 
                left join md_product mp on mp.product_id = mpo.product_id 
                left join md_user mu on mu.user_id = mpo.user_id 
                left join md_address ma on ma.address_id = mpo.address_id 
            ) as o where 1 = 1 {0} limit 1", sqlWhere);
        #endregion

        var countRes = QueryCount(sqlCountStr, model);

        var page = new Page<MdProductOrderModel>();

        page.Items = list.ToList();
        page.TotalItems = countRes;
        page.CurrentPage = pageIndex;
        page.PageSize = pageSize;
        page.TotalPages = (int)Math.Ceiling(page.TotalItems / (double)page.PageSize);

        return page;
    }

    /// <summary>
    /// 通过编号删除订单
    /// </summary>
    /// <param name="ids">编号多个逗号分隔</param>
    /// <returns>int</returns>
    public int DeleteMdProductOrderById(string ids)
    {
        string sqlWhere = " and `order_id` in @OrderIds ";

        return Delete(sqlWhere, new { OrderIds = ids.Split(',').ToArray() });
    }

    /// <summary>
    /// 通过订单编号更新订单
    /// </summary>
    /// <param name="model">请求实体</param>
    /// <returns>long</returns>
    public long UpdateMdProductOrderModelById(MdProductOrderModel model)
    {
        var sqlSet = string.Empty;

        #region ===set===
        if (model.ProductId > 0)
        {
            sqlSet += " `product_id` = @product_id, ";
        }

        if (model.UserId > 0)
        {
            sqlSet += " `user_id` = @UserId, ";
        }

        if (model.AddressId > 0)
        {
            sqlSet += " `address_id` = @AddressId, ";
        }

        if (model.CallbackUrl != null)
        {
            sqlSet += " `callback_url` = @CallbackUrl, ";
        }

        if (!string.IsNullOrWhiteSpace(model.Status))
        {
            sqlSet += " `status` = @Status, ";
        }

        if (model.Reason != null)
        {
            sqlSet += " `reason` = @Reason, ";
        }

        if (model.Remark != null)
        {
            sqlSet += " `remark` = @Remark, ";
        }

        if (!string.IsNullOrWhiteSpace(model.UpdateBy))
        {
            sqlSet += " `update_by` = @UpdateBy, ";
        }

        sqlSet += " `update_time` = sysdate() ";
        #endregion

        string sqlWhere = " and `del_flag` = '0' and `order_id` = @OrderId ";

        return Update(sqlSet, sqlWhere, model);
    }
}
